package dk.muldbjergskolen.db;

import java.sql.*;
import java.util.ArrayList;

import dk.muldbjergskolen.model.*;

public class FagDB {

	private Connection con;

	public FagDB() {
		con = ConnectionDB.getInstance().getDBcon();
	}

	// Finder alle fagene og returnere dem i en ArrayListe
	public ArrayList<Fag> findFagene() {
		ArrayList<Fag> aF = new ArrayList<Fag>();
		String query = "Select id, navn, forkortelse from Fag";
		Fag f = null;

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				f = buildFag(rs);
				aF.add(f);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return aF;
	}

	// Laver et fag ud fra databasen
	public Fag buildFag(ResultSet result) {
		Fag f = null;

		try {
			f = new Fag(result.getInt("id"), result.getString("navn"),
					result.getString("forkortelse"));
		} catch (Exception e) {
			System.out.println("Fejl i opbygningen af et fag.");
		}

		return f;
	}

	// Finder et fag og returnere det
	public Fag findFag(String navn) {
		Fag f = null;
		String query = "select * from Fag where navn = '" + navn + "'";
		try { // update fag
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ResultSet rs = stmt.executeQuery(query);

			if (rs.next()) {
				f = buildFag(rs);
			}

			return f;
		}

		catch (Exception e) {
			System.out.println("..");
		}

		return f;

	}

	// Opretter et fag
	public void opretFag(Fag f) throws SQLException {
		String query = "insert into Fag (navn, forkortelse) values ('"
				+ f.getNavn() + "' , '" + f.getForkortelse() + "')";
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			throw e;
		}
	}

	// updater fag ud fra id
	public int updateFag(Fag f, int glId) {
		Fag fObj = f;
		int rc = -1;

		String query = "UPDATE Fag SET navn='" + fObj.getNavn()
				+ "' ,forkortelse ='" + fObj.getForkortelse()
				+ "' where id = '" + glId + "'";

		System.out.println("Update query:" + query);
		try { // update fag
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}// slut try
		catch (Exception ex) {
			System.out.println("Update exception in fag db: " + ex);
		}
		return (rc);
	}

}
